package com.cloud.dao.assetsdetail;

import com.cloud.model.entity.assetsdetail.AssetsDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import sun.awt.SunHints;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

/**
 * <b><code>AssetsDetailDao</code></b>
 * <p/>
 * AssetsDetail的具体实现
 * <p/>
 * <b>Creation Time:</b> 2019-10-15 17:22:19.
 *
 * @author ${model.author}
 * @version 1.0.0
 * @since cloud-spring-boot-be 1.0.0
 */
@Repository
public interface AssetsDetailDao extends JpaRepository<AssetsDetail, Long>, JpaSpecificationExecutor<AssetsDetail> {

    AssetsDetail findByUserIdAndNowFirstMonth(Long userId,Long nowFirstMonth);


    @Modifying
    @Query(value = "update t_assets set total_income = ?1, total_expenditure = ?2, total_money = ?3,update_time = ?4 where id = ?5",nativeQuery = true)
    void updateAssetsMoneyAndUpdateTime(Long totalIncome,Long totalExpenditure,Long totalMoney,Long updateTime,Long id);

    @Query(value = "select sum(month_total_income) as income,sum(month_total_expenditure) as expenditure,sum(month_total_money) as money from t_assets_detail where assets_id = ?1",nativeQuery = true)
    Map<String,Long> sunMoneyByAssetsId(Long assetsId);

    AssetsDetail findByAssetsIdAndNowFirstMonth(Long id,Long nowFirstMonth);

    AssetsDetail findByEnabledAndUserIdAndNowFirstMonth(Integer enabled, Long userId, Long monthFirst);

    List<AssetsDetail> findByAssetsId(Long id);

    @Query(value = "SELECT a.categorie, IFNULL(b.pay, 0) AS 'pay',IFNULL(b.income,0) AS 'income' ,(IFNULL(b.income,0) - IFNULL(b.pay,0)) AS 'totalMoney' FROM (\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL + 0 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL + 1 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 2 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 3 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 4 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 5 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 6 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 7 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 8 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 9 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 10 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 11 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 12 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 13 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 14 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 15 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 16 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 17 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 18 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 19 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 20 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 21 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 22 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 23 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 24 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 25 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 26 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 27 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 28 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 29 DAY ) as categorie UNION ALL\n" +
            " SELECT DATE_ADD(FROM_UNIXTIME(?1 / 1000, '%Y-%m-%d'),INTERVAL  + 30 DAY ) as categorie ) AS a \n" +
            " LEFT JOIN (\n" +
            "  SELECT FROM_UNIXTIME(tb.consume_time_day /1000,'%Y-%m-%d') AS datetime,sum(if(tct.type = 0,tb.money,0)) AS 'pay', sum(if(tct.type = 1,tb.money,0)) AS 'income'\n" +
            "  FROM t_bookkeeping as tb\n" +
            "  left join t_consume_type tct on tb.consume_type_id = tct.id\n" +
            "  where tb.user_id = ?2 and tb.consume_time_day in ?3\n" +
            "  GROUP BY tb.consume_time_day\n" +
            "  ) AS b ON a.categorie = b.datetime group by a.categorie asc\n",nativeQuery = true)
    List<Map<String,Object>> findByNowFirstMonthAndUserIdAndMonthValues(Long startTimeValue, Long userId, List<Long> nowTimesValue);
}
